package org.ssp.util;

import java.io.File;
import java.io.IOException;
import java.nio.channels.WritableByteChannel;
import java.util.ArrayList;

import org.ssp.pojo.School;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * java操作excel工具类
 *  通过excel批量导入数据
 *  
 * */

public class ExcelUtil {
	private final String PATH="";
	/*
	 * 从 excel表中解析出所有条数及字段
	 * */
	public static ArrayList<School> getDataByExcel(String path){
		ArrayList<School> list=new ArrayList<School>();
		try {
			Workbook rwb=Workbook.getWorkbook(new File(path));
		    Sheet rs=rwb.getSheet(0);
		    int clos=rs.getColumns();//得到所有的列
		    int rows=rs.getRows();//得到所有的行
		  
		    for(int i =2;i<rows;i++){
		    	for(int j=0;j<clos;){
		    		String name=rs.getCell(j++,i).getContents();
		    		String content=rs.getCell(j++,i).getContents();
		    		String rank=rs.getCell(j++,i).getContents();
		    		System.out.println("rank...."+rank);
		    	    list.add(new School(name, content, Integer.valueOf(rank)));
		    	}
		    }
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 
	 **/
	public static ArrayList<School> getDataByExcel2(String path){
		ArrayList<School> list=new ArrayList<School>();
		try {
			Workbook rwb=Workbook.getWorkbook(new File(path));
		    Sheet rs=rwb.getSheet(0);
		    int clos=rs.getColumns();//得到所有的列
		    int rows=rs.getRows();//得到所有的行
		  
		    for(int i =2;i<rows;i++){
		    	for(int j=0;j<clos;){
		    		String name=rs.getCell(j++,i).getContents();
		    		String content=rs.getCell(j++,i).getContents();
		    	    list.add(new School(name, content, 0));
		    	    
		    	}
		    }
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 写数据到excel
	 * 
	 */
	public void writeToExcel(String path,School school){
		File file=new File(path);
		if(!file.exists()){
			try {
				file.createNewFile();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		try {
			WritableWorkbook wwb=Workbook.createWorkbook(file);
		    WritableSheet ws= wwb.createSheet("export", 0);
		    Label labelname=new Label(0,0,"学校名称");
		    Label labelcontent=new Label(1,0,"链接");
		    Label labelrank=new Label(2,0,"学校排名");
		    ws.addCell(labelname);
		    ws.addCell(labelcontent);
		    ws.addCell(labelrank);
		    int seq=1;
		    
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
	}
}
